import os
import re
import sqlite3
# commit()--事务提交
# rollback()--事务回滚
# close()--关闭一个数据库连接
# cursor()--创建一个游标

# '评论人': user_name,
# '评论头像': user_face,
# '评论时间': user_date,
# '评论内容': user_text,
# '购买商品类型': info,
# '评论视频截图': videoimg,
# '评论视频': videoUrl,
# '好评图片': imglist,

#创建表
# from web.settings import BASE_DIR

# 标题id, 链接a, 状态b, 淘商品链接c, 淘店铺链接d, 急速图片e, 急速内容f, 生成评论内容g, 备用h,作者i,内容j,
import time
from concurrent import futures

from maokoulingClass import userid_to_shopTBK_link, maokouling_ONE, tblink_get_gaoyong, item_to_SELLID
from tbmain import tbpinglun


def initdb(cursor):
    create_table_sql = '''create table IF NOT EXISTS JiSuBBS(
                        '标题' INTEGER,
                        '链接' text unique,
                        '状态' text,
                        '淘商品链接' text,
                        '淘店铺链接' text,
                        '急速图片' text,
                        '急速内容' text,
                        '生成评论内容' text,
                        '备用' text,
                        '作者' text,
                        '内容' text
                        )'''
    cursor.execute(create_table_sql)  # 执行这个语句

#改2
# 状态=?,
# 淘商品链接=?,
# 淘店铺链接=?,
# 急速图片=?,
# 急速内容=?,
# 生成评论内容=?,
# 标题id, 链接a, 状态b, 淘商品链接c, 淘店铺链接d, 急速图片e, 急速内容f, 生成评论内容g, 备用h
def updatedb(cursor,link,beiyong,itemlink,shoplink):
    update_sql = '''
        UPDATE
            JiSuBBS
        SET
            备用=?,
            淘商品链接=?,
            淘店铺链接=?
        WHERE
            链接=?
        '''
    cursor.execute(update_sql,(beiyong,itemlink,shoplink,link))
    print('修改成功')


# 改1
# 标题id, 链接a, 状态b, 淘商品链接c, 淘店铺链接d, 急速图片e, 急速内容f, 生成评论内容g, 备用h
def updateONEdb(cursor,itemid,shipid,link):
    update_sql = '''
        UPDATE
            JiSuBBS
        SET
            itemid=?,
            shopid=?
        WHERE
            链接=?
        '''
    cursor.execute(update_sql,(itemid,shipid,link))
    print(f'---{itemid}{shopid}插入成功')



#增
def insertdb(cursor,id,a,b,c,d,e,f,g,h,i,j):
    # 二、插入数据：执行语句即可
    # conn = sqlite3.connect("tb.db")
    # cursor=conn.cursor()
    username = "ytouch"
    password = "123456"
    user_id = 1
    insert_data_sql = '''insert or ignore into
                    JiSuBBS(
                        标题,
                        链接,
                        状态,
                        淘商品链接,
                        淘店铺链接,
                        急速图片,
                        急速内容,
                        生成评论内容,
                        备用,
                        作者,
                        内容
                    )
                    values (
                    :biaoti,
                    :lianjie,
                    :zhuangtai,
                    :itemlink,
                    :shoplink,
                    :jishuimg,
                    :jisucontent,
                    :makecontent,
                    :beiyong,
                    :zuozhe,
                    :contents
                    )'''
    cursor.execute(insert_data_sql,
                {
                'biaoti': id,
                'lianjie': a,
                'zhuangtai': b,
                'itemlink': c,
                'shoplink': d,
                'jishuimg': e,
                'jisucontent': f,
                'makecontent': g,
                'beiyong': h,
                'zuozhe': i,
                'contents': j
                })

#把这两个函数不能直接用,把他门直接复制到想使用的地方
def opendb():
    conn = sqlite3.connect('tb.db')
    cursor = conn.cursor()

def closedb(conn,cursor):
    conn.commit()
    cursor.close()


#查询表内所有数据
def searchLINKdb(cursor):
    '''查询语句'''
    search_sql = "select 链接,状态 from JiSuBBS"
    results = cursor.execute(search_sql)
    all_results = results.fetchall()
    print(all_results)
    for row in all_results:
        print(type(row))  # type:tuple
        print(list(row))  # type:list
        if list(row)[1] !='未使用':
            print('已经修改过了')
            all_results.remove(row)
    # print(list(all_results))
    return all_results



#查询表内所有数据
def searchdb(cursor):
    '''查询语句'''
    search_sql = "select 标题,链接,状态,淘商品链接,淘店铺链接,急速图片,急速内容,生成评论内容,备用,作者,内容 from JiSuBBS"
    results = cursor.execute(search_sql)
    all_results = results.fetchall()
    # for row in all_results:
        # print(type(row))  # type:tuple
        # print(list(row))  # type:list
    return all_results
#查询表内所有内容连接数据
def searchcontentdb(cursor,rowid):
    '''查询语句'''
    search_sql = f"select 链接,内容,备用,itemid,shopid from JiSuBBS where rowid = {rowid}"
    # search_sql = "select count(*)  from JiSuBBS"
    results = cursor.execute(search_sql)
    all_results = results.fetchone()
    # for row in all_results:
        # print(type(row))  # type:tuple
        # print(list(row))  # type:list
    return all_results
#根据id查询表内符合LINK的数据
def searchLink(cursor,link):
    '''查询语句'''
    search_sql = f"SELECT * FROM JiSuBBS WHERE 链接 = {link}"
    results = cursor.execute(search_sql)
    all_results = results.fetchall()
    # for row in all_results:
        # print(type(row))  # type:tuple
        # print(list(row))  # type:list
    print(all_results)
    return all_results
#根据作者列查询所有的符合内容
def searchZuoZhe(cursor,zuozhe):
    '''查询语句'''
    search_sql = f"SELECT * FROM JiSuBBS WHERE 作者 = {zuozhe}"
    results = cursor.execute(search_sql)
    all_results = results.fetchall()
    # print(len(all_results))
    # for row in all_results:
        # print(type(row))  # type:tuple
        # print(list(row))  # type:list
    # print(all_results)
    return all_results

#查询作者列去掉重复的内容所有名字
def seachzuozheNames(cursor):
    search_sql = "SELECT DISTINCT 作者 FROM JiSuBBS"
    results = cursor.execute(search_sql)
    all_results = results.fetchall()
    namelist = []
    for name in all_results:
        namelist.append(name[0])
        with open('作者.txt', 'a+', encoding='utf-8') as f:
            f.write(name[0] + '\n')
    print(namelist)

    return namelist
    # print(all_results)

#删除表内某个商品id的所有评论的内容    delete(cursor,'588976817331')
def delete(cursor,link):
    delete_sql = f'delete from JiSuBBS where 链接 = {link}'
    cursor.execute(delete_sql)

#删除表内某个作者的所有评论的内容    delete(cursor,'588976817331')
def deleteZUOZHE(cursor,link):
    delete_sql = f'delete from JiSuBBS where 作者 = {link}'
    cursor.execute(delete_sql)
# 删除一整个表格    deleteTable(cursor, "happy")
def deleteTable(cursor, tablename):
    delete_all_sql = f'drop table {tablename}'
    cursor.execute(delete_all_sql)
#查询
##匹配所有的内容，然后里边有sclick的连接的留下，其他的删除
def panduan_sclick(link,content):
    panduan_sclik = 's.click.taobao.com'
    if panduan_sclik in content:
        print('符合要求')
    else:
        print("垃圾网页")
        # 没有该链接的删除这一行
        delete(cursor, f"'{link}'")
        conn.commit()
def runmain(num,cursor):
    print(num)
    searchresult = searchcontentdb(cursor,num)
    # print(searchresult)
    try:
        panduan_sclick(searchresult[0],searchresult[1])
        print('成功')
    except:
        print('已经删除')
    # time.sleep(5)
def ThreadLine(cursor):
    print('213')
    with futures.ThreadPoolExecutor(max_workers=1) as executor:  # 多线程
        # for循环进行多线程调用
        for i in range(36480):
            # 在循环内实例化，在外边实例化会让所有的值一致
            executor.submit(runmain, i,cursor)

def addnewclous(cursor):
    cursor.execute(f'ALTER TABLE JiSuBBS ADD COLUMN shopid INTEGER')

if __name__ == '__main__':
    dbpath = os.path.join('JiSu.db')
    print(dbpath)
    conn = sqlite3.connect(dbpath)
    cursor = conn.cursor()
    # list =searchLINKdb(cursor)
    # list = searchLink(cursor,"'thread-1006510-1-27.html'")
    # zuozheList = seachzuozheNames(cursor)
    # insertdb(cursor, '123', '123', '213', '', '', '', '', '', '','','123')

    #根据作者名字查询所有的内容--START
    # for name in zuozheList:
    #     num = searchZuoZhe(cursor,f"'{name}'")
    #     with open('作者发表数量.txt', 'a+', encoding='utf-8') as f:
    #         f.write(name+'----发表----'+str(len(num)) + '\n')
    #根据作者名字查询所有的内容--END


    #删除不要的作者--START
    # deleteZUOZHE(cursor,f"'云云的小猪'")
    #删除不要的作者--END


    #查询作者发表的网页们--START
    # name ='要优惠就找我'
    # num=0
    # linklist = []
    # for name in zuozheList:
    #     contentwebs = searchZuoZhe(cursor, f"'好货哥'")
    #     for content in contentwebs:
    #         # print(type(content))
    #         print(content[10])
    #         print(content[1])
    #         str1 = re.compile(r'https://s.click')
    #         strlist = str1.findall(content[10])
    #         print(strlist)
    #         num = num+1
    #         print(num)
    #         if strlist == []:
    #             print('没有链接')
    #         else:
    #             break
    #             with open('带淘宝客链接的链接列表.txt', 'a+', encoding='utf-8') as f:
    #                 f.write(content[1] + '\n')
    #
    #查询作者发表的网页们--END

    #增加新列
    # addnewclous(cursor)

    #增加id段


    # 删除没有淘宝客连接的数据
    # for num in range(4,36480):
    #     runmain(num,cursor)
    #     ################################################

    #批量从内容里边筛选sclick链接，匹配成商品ID  商铺userid
    def getitemID(cursor):
        timenum = 0
        for num in range(1, 36500):
            searchresult = searchcontentdb(cursor, num)
            # print(searchresult)
            if searchresult == None:
                continue
            else:
                if searchresult[2] == '1':
                    # 已经注册 itemid shop id
                    continue
                # 进行链接转换
                print('链接转换' + str(num))
                sclick_str = re.compile('([a-zA-z]+://s.click.taobao.com[^\s]*)["]')
                result = sclick_str.findall(searchresult[1])
                # print(result)
                itemid = 0
                shopid = 0
                linknum = 0
                for link in result:
                    if linknum >= 2:
                        continue
                    linknum = linknum + 1
                    # 遍历里边的链接，进行测试拿到商品id
                    # print(link)
                    from TESS import getItemID, linktolink
                    result = linktolink(link)
                    timenum = timenum + 1
                    if timenum == 1000:
                        timenum = 0
                        time.sleep(10)
                    # print(f'链接的内容是----{link}')
                    if result == None:
                        # 链接或者是商铺id 或者是错误的链接
                        # print('11111111111111')
                        result = getItemID(link)
                    else:
                        itemid = result
                        break
                    # print(result)
                    print(link)
                    if len(result) <= 10:
                        # 店铺id
                        shopid = result
                        print('--链接转换店铺id')
                        if shopid == '111':
                            # time.sleep(30)
                            print('--------------------------访问频繁受限')

                    else:
                        print('--链接转换错误')
                updateONEdb(cursor, itemid, shopid, searchresult[0])
                conn.commit()
                # 设置成功后把备用设置为1
                cursor.execute(f'UPDATE JiSuBBS SET 备用=1 WHERE 链接="{searchresult[0]}"')
                conn.commit()
                print(f'-----{num}运行结束，商品id={itemid},店铺id={shopid}')
                print('\n')
                # time.sleep(4)
                # break



    #删除表格里边shopid 为111的所有行
    def deletenousedrow(cursor):
        print('123')
        cursor.execute('delete from JiSuBBS WHERE shopid = 111')
        # print(result.fetchone())
        conn.commit()
        print('231')
        # all_results = results.fetchone()
        # print(all_results)
    deletenousedrow(cursor)


    #把内容列变成0
    def updaterow(cursor):
        cursor.execute('UPDATE JiSuBBS SET 内容 =0')
        conn.commit()
    # updaterow(cursor)



    #把每个id 转换成需要的itemlink和shoplink
    def getitemlinkANDshop(cursor):
        timenum = 0
        for num in range(1, 36500):
            searchresult = searchcontentdb(cursor, num)
            # print(searchresult)
            if searchresult == None:
                continue
            else:
                if searchresult[2] == '2':
                    # 已经注册 itemid shop id
                    continue
                # 进行链接转换
                print('链接转换' + str(num))
                itemid = searchresult[3]
                shopid = searchresult[4]
                abc = maokouling_ONE()
                if itemid ==0:
                    #商品id为空,请求shopid
                    itemlink = 0
                    abc.userid =shopid
                    shoplink = userid_to_shopTBK_link(abc)
                else: #有商品id，看有没有店铺id，有id'的话esle处理，直接获得店铺id ，没有id的话获取userid再赋值
                    abc.itemid =itemid
                    itemlink = tblink_get_gaoyong(abc)
                    if shopid ==0:
                        abc.userid = item_to_SELLID(abc)
                        shoplink = userid_to_shopTBK_link(abc)
                    else:
                        abc.userid = shopid
                        shoplink = userid_to_shopTBK_link(abc)
                print(itemlink)
                print(shoplink)
                # 设置成功后把备用设置为1
                cursor.execute(f'UPDATE JiSuBBS SET 备用=2,淘商品链接="{itemlink}", 淘店铺链接="{shoplink}" WHERE 链接="{searchresult[0]}"')
                conn.commit()
                print(f'-----{num}运行结束，商品id={itemid},店铺id ={shopid}')
                print('\n')
                # time.sleep(4)
                # break
    # getitemlinkANDshop(cursor)

    ##删除商品link 和shoplink都是空的链接
    def deleteShopAndItemid(cursor):
        cursor.execute("delete from JiSuBBS WHERE 淘商品链接 = 0 and 淘店铺链接 ='None'")

    deleteShopAndItemid(cursor)


    #取出所有不重复的商品id看看有多少
    def findids(cursor):
        # results = cursor.execute('SELECT DISTINCT itemid FROM JiSuBBS')
        # all_results = results.fetchall()
        # print(len(all_results))
        def writetxt():
            listid=[]
            for line in open('links1.txt', errors='ignore'):
                # 清洗空格后如果不为空就下一步处理
                if line.strip() != '':
                    print(f"本行内容为{line}")
                    # 每行的数据都需要清洗空格后进行下一步处理
                    abc = line.strip()
                    listid.append(abc)
                else:
                    print("本行为空")
            return listid
        listid = writetxt()
        for ids in listid:
            #取出每一个id
            print(id)
            tbpinglun(ids)
            time.sleep(3)
    # 利用搜寻的id唯一值取获取响应的评论数据，存储到数据库里边
    # findids(cursor)

    cursor.execute('update JiSuBBS set title = 标题 ')
    conn.commit()
    cursor.close()